-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		duylv00360
-- Create date: 
-- Description:	
-- =============================================
ALTER PROCEDURE InsertMaterialLoaf 
	-- Add the parameters for the stored procedure here
	@IsImport bit = 1,
	@OrderID int = 0, 
	@MaterialID int = 0,
	@WarehouseID int = 0,
	@Quantity numeric(10,0) = 0,
	@PricePerUnit numeric(10,0) = 0,
	@result int = 0 OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    --Begin transaction 
    BEGIN TRANSACTION 
		INSERT INTO MaterialLoaf(
					OrderID,
					MaterialID,
					WarehouseID,
					Quantity,
					PricePerUnit
					)
		VALUES		(
					@OrderID,
					@MaterialID,
					@WarehouseID,
					@Quantity,
					@PricePerUnit
		
		)
	SAVE TRANSACTION InsertMaterialLoaf
	IF @@ERROR <> 0 ROLLBACK TRANSACTION InsertMaterialLoaf
	IF @IsImport = 1
		BEGIN
			IF EXISTS (SELECT WarehouseMaterial.MaterialID FROM WarehouseMaterial WHERE WarehouseMaterial.MaterialID = @materialID AND WarehouseMaterial.WarehouseID = @warehouseID)
				BEGIN
					UPDATE	WarehouseMaterial
					SET		AveragePricePerUnit = (AveragePricePerUnit * Quantity + @Quantity * @PricePerUnit)/(Quantity + @Quantity),Quantity = Quantity+@quantity
					WHERE	WarehouseMaterial.MaterialID = @materialID
							AND 
							WarehouseMaterial.WarehouseID = @warehouseID		
					
					--check if have error roll back transaction
					SAVE TRANSACTION InsertMaterialLoaf
					IF @@ERROR <> 0 ROLLBACK TRANSACTION InsertMaterialLoaf
					-- set @result = 1
					SET @result = 1;
				END
			ELSE
				BEGIN
					INSERT INTO WarehouseMaterial(MaterialID, WarehouseID, Quantity,AveragePricePerUnit)
					VALUES ( @materialID, @warehouseID, @quantity,@PricePerUnit);	
					--check if have error roll back transaction
					SAVE TRANSACTION InsertMaterialLoaf
					IF @@ERROR <> 0 ROLLBACK TRANSACTION InsertMaterialLoaf
					-- set @result = 1
					SET @result = 1;			
					
				END 
			SAVE TRANSACTION InsertMaterialLoaf
			IF @@ERROR <> 0 ROLLBACK TRANSACTION InsertMaterialLoaf
		END
	ELSE
		BEGIN
			DECLARE @totalQuantity int = 0;
			SET @totalQuantity = (SELECT WarehouseMaterial.Quantity FROM WarehouseMaterial WHERE WarehouseMaterial.MaterialID = @materialID AND WarehouseMaterial.WarehouseID = @warehouseID);
			IF (@quantity <= @totalQuantity)
				BEGIN
					--INSERT INTO MaterialLoaf
					--VALUES(@materialID, @orderID, @warehouseID, @quantity, @pricePerUnit);
					--SAVE TRANSACTION InsertMaterialLoaf
					--IF @@ERROR <> 0 ROLLBACK TRANSACTION InsertMaterialLoaf
					UPDATE	WarehouseMaterial
					SET		Quantity = Quantity - @quantity
					WHERE	WarehouseMaterial.MaterialID = @materialID
							AND 
							WarehouseMaterial.WarehouseID = @warehouseID;	
					SAVE TRANSACTION InsertMaterialLoaf
					IF @@ERROR <> 0 ROLLBACK TRANSACTION InsertMaterialLoaf
					-- set @result = 0
					SET @result = 0;				 
				END
			ELSE
				BEGIN
					PRINT 'Not enough material in this warehouse!';		
					-- set @result = -1
					SET @result = -1;			
				END
			
			SAVE TRANSACTION InsertMaterialLoaf
			IF @@ERROR <> 0 ROLLBACK TRANSACTION InsertMaterialLoaf
		END	
	COMMIT TRANSACTION	
END
GO

/*
use WarehouseManagement
go
exec InsertMaterialLoaf @orderID= 48,@materialID= 2,@warehouseID= 1, @quantity = 400,@priceperunit= 14.60, @isimport = 0
*/